SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[FN_TMDOCC_GENE_Q10]
/*--------------------------------------------------------------------------*/
/*---- Empresa		   : OFISIS S.A					----*/
/*---- Cliente		   : C.P.P.Q					----*/
/*---- Sistema		   : Tesoreria        				----*/
/*---- Modulo		   : Empresa           				----*/
/*---- Programa		   : Devuelve el Importe Pagado en Rang Fechas 	----*/
/*---- Script		   : TMCTA_CTE.SQL				----*/
/*---- Nombre SP	   : FN_TMDOCC_GENE_Q10				----*/
/*---- Desarrollado por	   : Carlos MARQUEZ ESCOBAR			----*/
/*---- Fecha Creacion	   : 16/07/2004					----*/
/*---- Base de Datos	   : SQL					----*/
/*---- Version		   : 2000					----*/
/*--------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------*/
/*---- Comentarios         : Recorre los Diferentes pagos               ---*/
/*---- Drop FUNCTION FN_TMDOCC_GENE_Q10       				----*/
/*---- Grant ALL on FN_TMDOCC_GENE_Q10 to public           		----*/
/*---- 									----*/
/*--------------------------------------------------------------------------*/
/*---- 
Declare	@IM_PAGO TD_NU_016_004
Select	@IM_PAGO = dbo.FN_TMDOCC_GENE_Q10('01', 'FAC', '0601-0000039020', '2033805411500', 'DOL', '1999-09-01','2004-07-30', 'S','SOL', 'DOL','C')
Select	@IM_PAGO							---*/
/*---- 									----*/
/*--------------------------------------------------------------------------*/
(
@ISCO_EMPR		TD_VC_002,
@ISCO_TIPO_DOCU		TD_VC_003,
@ISNU_DOCU_CLIE		TD_VC_020,

@ISCO_CLIE  		TD_VC_020,
@ISCO_MONE 		TD_VC_003,
@IDFE_INIC_CANC		TD_DT_001,
@IDFE_CANC		TD_DT_001,
@ISST_SIGN		TD_VC_001,
@ISCO_MONE_NACI		TD_VC_003,
@ISCO_MONE_EXTR		TD_VC_003,
@ISCO_TIAU_CLIE		TD_VC_001

)	RETURNS TD_NU_016_004

As 
BEGIN
	Declare 
/*
@ISCO_EMPR		TD_VC_002,
@ISCO_TIPO_DOCU		TD_VC_003,
@ISNU_DOCU_CLIE		TD_VC_020,

@ISCO_CLIE  		TD_VC_020,
@ISCO_MONE 		TD_VC_003,
@IDFE_INIC_CANC		TD_DT_001,
@IDFE_CANC		TD_DT_001,
@ISST_SIGN		TD_VC_001,
@ISCO_MONE_NACI		TD_VC_003,
@ISCO_MONE_EXTR		TD_VC_003,
@ISCO_TIAU_CLIE		TD_VC_001,

*/	
	@ONIM_PAGA	TD_NU_016_004,
	@VNIM_PAG1	TD_NU_016_004,
	@VNIM_PAGD	TD_NU_016_004,
	@VNIM_PAG2	TD_NU_016_004,
	@VNIM_PAG3	TD_NU_016_004,
	@VNIM_PAG4	TD_NU_016_004,
	@VNIM_PAG5	TD_NU_016_004,
	@VNIM_PAG6	TD_NU_016_004,
	@VNIM_CXCB	TD_NU_016_004

/*
set @ISCO_EMPR	= '01'
set @ISCO_TIPO_DOCU	= 'LET'
set @ISNU_DOCU_CLIE	= '28'

set @ISCO_CLIE	= 'RRRR'
set @ISCO_MONE	= 'DOL'
set @IDFE_INIC_CANC	= '1999-10-01'
set @IDFE_CANC		= '2004-07-28'
set @ISST_SIGN		= 'S'
set @ISCO_MONE_NACI	= 'SOL'
set @ISCO_MONE_EXTR	= 'DOL'
set @ISCO_TIAU_CLIE	= 'C'

*/
	Set @ONIM_PAGA	= 0
	Set @VNIM_PAG1	= 0
	Set @VNIM_PAGD	= 0
	Set @VNIM_PAG2	= 0
	Set @VNIM_PAG3	= 0
	Set @VNIM_PAG4	= 0
	Set @VNIM_PAG5	= 0
	Set @VNIM_PAG6	= 0
	Set @VNIM_CXCB	= 0

	/**** Depurado ******/
	--1.- DOC CONSULTADO EN ESA FECHA

	--2.- PAGOS BANCOS y/o CAJA
	Select	@VNIM_PAG2 = 
		( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
		Sum(PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +
		 PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU )
	From	TDMOVI_BANC T1, TCMOVI_BANC T2
	Where	T1.CO_EMPR = @ISCO_EMPR
	  And	T1.TI_ENTI = @ISCO_TIAU_CLIE
	  --And	T1.CO_ENTI = @ISCO_CLIE
	  And	T1.TI_DOCU_ENTI = @ISCO_TIPO_DOCU
	  And	T1.NU_DOCU_ENTI = @ISNU_DOCU_CLIE
	  And	T2.FE_OPER  > @IDFE_INIC_CANC
	  And	T2.FE_OPER  <= @IDFE_CANC
	  And	T2.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T2.CO_EMPR  
	  And	T1.CO_BANC = T2.CO_BANC  
	  And	T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
	  And	T1.AA_BNCO = T2.AA_BNCO
	  And	T1.MM_BNCO = T2.MM_BNCO 
	  And	T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
	  And	T1.NU_COMP_BANC = T2.NU_COMP_BANC
	
	  And	T1.CO_EMPR = @ISCO_EMPR
	  And	T1.TI_DOCU_ENTI = @ISCO_TIPO_DOCU
	  And	T1.NU_DOCU_ENTI = @ISNU_DOCU_CLIE


		-- Cheques Diferidos
	Select	@VNIM_PAGD = 	 Sum(t1.IM_CHEQ)
	From	TDCHEQ_BANC T1, TCMOVI_BANC T2
	Where	T1.CO_EMPR = @ISCO_EMPR
	  And	T1.TI_ENTI = @ISCO_TIAU_CLIE
	  --And	T1.CO_ENTI = @ISCO_CLIE
	  And	'CHE' = @ISCO_TIPO_DOCU
	  And	T1.NU_CHEQ = @ISNU_DOCU_CLIE

	  And	T2.FE_OPER  > @IDFE_INIC_CANC	
	  And	T2.FE_OPER  <= @IDFE_CANC
	  And	T2.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T1.CO_EMPR 
	  And	T1.CO_BANC = T2.CO_BANC 
	  And	T1.NU_CNTA_BANC = T2.NU_CNTA_BANC
	  And	T1.AA_BNCO= T2.AA_BNCO
	  And	T1.MM_BNCO = T2.MM_BNCO
	  And	T1.TI_MOVI_BANC = T2.TI_MOVI_BANC 
	  And	T1.NU_COMP_BANC = T2.NU_COMP_BANC
	
		-- Caja
	Select	@VNIM_PAG2 = IsNull(@VNIM_PAG2,0) +  
		IsNull(( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
		Sum(PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +
		 PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU ), 0)
	From   TDMOVI_CAJA T1, TCMOVI_CAJA T2
	Where	T1.CO_EMPR = @ISCO_EMPR
	  And	T1.TI_ENTI = @ISCO_TIAU_CLIE
	  --And	T1.CO_ENTI = @ISCO_CLIE
	  And	T1.TI_DOCU_ENTI = @ISCO_TIPO_DOCU
	  And	T1.NU_DOCU_ENTI = @ISNU_DOCU_CLIE

	  And	T2.FE_OPER  > @IDFE_INIC_CANC	
	  And	T2.FE_OPER  <= @IDFE_CANC
	  And	T2.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T2.CO_EMPR  
	  And	T1.CO_CAJA = T2.CO_CAJA
	  And	T1.AA_CAJA = T2.AA_CAJA
	  And	T1.MM_CAJA = T2.MM_CAJA 
	  And	T1.TI_MOVI = T2.TI_MOVI
	  And	T1.NU_COMP_CAJA = T2.NU_COMP_CAJA
	

	--3.- APLICACIONES NCR
	If @ISCO_TIPO_DOCU != 'NCR'
		-- Documentos Letras o Facturas	
		-- Documentos Letras o Facturas	
		Select	@VNIM_PAG3 =
			Sum(t3.IM_PAGA)
			From (	Select 		
					Case Max(T1.CO_MONE)
						When @ISCO_MONE Then  
									( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
									Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_DEST +
									 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DEST )
						Else
							Case	Max(T1.CO_MONE)
								When @ISCO_MONE_NACI Then  (	( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') )  * 
								Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_DEST ) / Max(T1.FA_TIPO_CAMB ) +
								Sum(PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DEST ) )
								Else  (	( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') )  * 
								Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_DEST ) * Max(T1.FA_TIPO_CAMB ) +
								Sum(PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DEST ) )
							End
					End IM_PAGA
				From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2
				Where	T2.CO_EMPR = @ISCO_EMPR
				
				  --And	T1.CO_CLIE = @ISCO_CLIE
				  And	T2.CO_TIDO_DEST = @ISCO_TIPO_DOCU
				  And	T2.NU_DOCU_DEST = @ISNU_DOCU_CLIE
				
				  And	T1.FE_AMAR  > @IDFE_INIC_CANC
				  And	T1.FE_AMAR  <= @IDFE_CANC
				  And	T1.TI_SITU != 'ANU' 
				
				  And	T1.CO_EMPR = T2.CO_EMPR  
				  And	T1.NU_AMAR = T2.NU_AMAR )t3
	
	Else
		-- Solo para Documentos Notas de Credito...
		Select	@VNIM_PAG3 = 
			Sum(t3.IM_PAGA)
			From ( Select 
				Case Max(T1.CO_MONE)
					When @ISCO_MONE Then 
								 ( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
								Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_ORIG +
								 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_CORI )
					Else
					Case	Max(T1.CO_MONE)
						When @ISCO_MONE_NACI Then  (	( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
										Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_ORIG +
										 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_CORI ) )/ Max(T1.FA_TIPO_CAMB )
						Else  (	( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
										Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_COBR_ORIG +
										 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_CORI ) )* Max(T1.FA_TIPO_CAMB )
					End
				End IM_PAGA
				From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2
				Where	T2.CO_EMPR = @ISCO_EMPR
				
				  --And	T1.CO_CLIE = @ISCO_CLIE
				  And	T2.CO_TIDO_ORIG = @ISCO_TIPO_DOCU
				  And	T2.NU_DOCU_ORIG = @ISNU_DOCU_CLIE
				
				  And	T1.FE_AMAR  > @IDFE_INIC_CANC
				  And	T1.FE_AMAR  <= @IDFE_CANC
				  And	T1.TI_SITU != 'ANU' 
				
				  And	T1.CO_EMPR = T2.CO_EMPR  
				  And	T1.NU_AMAR = T2.NU_AMAR ) t3
	
	
	
	--4.- CANJES
	-- Solo muestro los documentos que se cancelan, no las Letras Generadas   
	Select	@VNIM_PAG4 =  
		( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
		Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_CANJ_MDOC +
		 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_CANJ_MDOC )
	From	TCCANJ_CLIE T1 , TDCANJ_CLIE T2
	Where	T2.CO_EMPR = @ISCO_EMPR
	  --And	T2.CO_CLIE = @ISCO_CLIE
	  And	T2.CO_TIPO_DOCU = @ISCO_TIPO_DOCU
	  And	T2.NU_DOCU_CLIE = @ISNU_DOCU_CLIE

	  And	T1.FE_CANJ  > @IDFE_INIC_CANC	
	  And	T1.FE_CANJ  <= @IDFE_CANC
	  And	T1.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T2.CO_EMPR
	  And	T1.CO_CLIE = T2.CO_CLIE
	  And	T1.NU_CANJ = T2.NU_CANJ
	
	
	
	--5.- RENOVACIONES
	-- Solo muestro los documentos que se Renuevan, no las Letras Generadas   
	Select	@VNIM_PAG5 =  
		( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
		Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_RENO_MDOC +
		 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_RENO_MDOC )
	From	TCRENO_CLIE T1 , TDRENO_CLIE T2
	Where	T2.CO_EMPR = @ISCO_EMPR
	  --And	T2.CO_CLIE = @ISCO_CLIE
	  And	T2.CO_TIPO_DOCU = @ISCO_TIPO_DOCU
	  And	T2.NU_DOCU_CLIE = @ISNU_DOCU_CLIE
	
	  And	T1.FE_RENO  > @IDFE_INIC_CANC	
	  And	T1.FE_RENO  <= @IDFE_CANC
	  And	T1.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T2.CO_EMPR
	  And	T1.CO_CLIE = T2.CO_CLIE
	  And	T1.NU_RENO = T2.NU_RENO
	
	
	
	--6.- APLICACION DE DOCUMENTOS A ANTICIPOS
	-- Solo muestro los documentos que se Aplican, no las Letras Generadas   
	Select	@VNIM_PAG6 =  
		( PatIndex(@ISST_SIGN,'S') - PatIndex(@ISST_SIGN,'N') ) * 
		Sum(PatIndex(T2.ST_CIER_DOCU, 'N') * T2.IM_CONV_DOCU +
		 PatIndex(T2.ST_CIER_DOCU, 'S') * T2.IM_SALD_DOCU )
	From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2
	Where	T2.CO_EMPR = @ISCO_EMPR
	  And	T2.TI_ENTI = @ISCO_TIAU_CLIE
	  --And	T2.CO_ENTI = @ISCO_CLIE
	  And	T2.TI_DOCU_ENTI = @ISCO_TIPO_DOCU
	  And	T2.NU_DOCU_ENTI = @ISNU_DOCU_CLIE
	
	  And	T1.FE_AMAR  > @IDFE_INIC_CANC	
	  And	T1.FE_AMAR  <= @IDFE_CANC
	  And	T1.TI_SITU != 'ANU' 
	
	  And	T1.CO_EMPR = T2.CO_EMPR
	  And	T1.NU_AMAR = T2.NU_AMAR

	Select	@ONIM_PAGA =	IsNull(@VNIM_PAG1,0) + IsNull(@VNIM_PAGD,0) + IsNull(@VNIM_PAG2,0) + IsNull(@VNIM_PAG3,0) + 
				IsNull(@VNIM_PAG4,0) + IsNull(@VNIM_PAG5,0) + IsNull(@VNIM_PAG6,0)


	RETURN (@ONIM_PAGA)
    
END

/*------------------------ FIN ----------------------------*/

GO
